package com.eryos.android.cigarettecounter.database;

import java.util.List;
import java.util.Map;

import android.database.Cursor;
import android.util.Log;

import com.eryos.android.cigarettecounter.beans.SmokeAverage;
import com.eryos.android.cigarettecounter.beans.SmokeCounter;

/**
 * Project : CigaretteCounter Author : Olivier Combe URL : http://eryos.fr/CigaretteCounter/
 * 
 * Copyright(C)2011 - Olivier Combe
 * 
 * Licensed under the Apache License, Version 2.0(the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
 * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations under the License.
 * 
 * @version \$Revision.*\$
 * @author Olivier Combe
 */
public class MonthlyAdapterImpl implements ExtendedPeriodicAdapterInterface {

    private final CigaretteDatabase currentDatabase;

    /**
     * Default constructor.
     * 
     * @param db
     *            associated to the adapter
     */
    public MonthlyAdapterImpl(final CigaretteDatabase db) {
        currentDatabase = db;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int getCount() {
        Log.i(this.getClass().getSimpleName(), "get Monthly Count");
        String sqlQuery = "SELECT   count(1)Nb " + "FROM     " + DBConnector.DATABASE_TABLE + " "
                + "WHERE    strftime('%m', smoke_time) = " + DBFields.THIS_MONTH + "; ";

        int res = -1;

        Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

        if (mCursor != null) {
            mCursor.moveToFirst();
            res = mCursor.getInt(0);
        }

        return res;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public double getCost() {
        Log.i(this.getClass().getSimpleName(), "get Monthly Cost");
        String sqlQuery = "SELECT   round(sum(ifnull(smoke_valid,0)), " + DBTools.ROUNDING + ")Nb " + "FROM     "
                + DBConnector.DATABASE_TABLE + " " + "WHERE    strftime('%m', smoke_time) = " + DBFields.THIS_MONTH
                + "; ";

        double res = -1;

        Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

        if (mCursor != null) {
            mCursor.moveToFirst();
            res = mCursor.getDouble(0);
        }

        return res;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public double getAverage() {
        Log.i(this.getClass().getSimpleName(), "get Monthly Average");
        try {
            String sqlQuery = "SELECT sum(nb), count(nb), round(avg(nb), " + DBTools.ROUNDING + ")" + "FROM  " + "("
                    + "    SELECT   strftime('%d/%m/%Y', smoke_time)Day, count(1)Nb  " + "    FROM     "
                    + DBConnector.DATABASE_TABLE + " " + "    WHERE    strftime('%m', smoke_time) = "
                    + DBFields.THIS_MONTH + " " + "    GROUP BY strftime('%d/%m/%Y', smoke_time)"
                    + "    ORDER BY strftime('%d/%m/%Y', smoke_time)" + ");";

            double res = -1;

            Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

            if (mCursor != null) {
                mCursor.moveToFirst();
                res = mCursor.getDouble(2);
            }

            return res;
        } catch (Exception e) {
            Log.e(this.getClass().getSimpleName(), "getAverage", e);
            return -1;
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Map<Integer, SmokeCounter> getDetails() {
        Log.i(this.getClass().getSimpleName(), "get Monthly Details");
        Cursor mCursor = null;
        try {

            String sqlQuery = "SELECT   strftime('%d/%m/%Y', smoke_time)Day, count(1)Nb, strftime('%d', smoke_time)Day, strftime('%m', smoke_time)Month, strftime('%Y', smoke_time)Year  "
                    + "FROM     "
                    + DBConnector.DATABASE_TABLE
                    + " "
                    + "WHERE    strftime('%m', smoke_time) = "
                    + DBFields.THIS_MONTH
                    + " "
                    + "GROUP BY strftime('%d/%m/%Y', smoke_time)"
                    + "ORDER BY strftime('%d/%m/%Y', smoke_time); ";

            mCursor = currentDatabase.executeQuery(sqlQuery);

            return DBTools.parseCursorAsSmokeCounterSet(mCursor);
        } catch (Exception e) {
            Log.e(this.getClass().getSimpleName(), "getDetails", e);
            return null;
        } finally {
            Log.i(this.getClass().getSimpleName(), "Closing Cursor");
            mCursor.close();
        }

    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Map<Integer, SmokeCounter> getPreviousDetails() {
        Log.i(this.getClass().getSimpleName(), "get Previous Monthly Details");
        try {

            String sqlQuery = "SELECT   strftime('%d/%m/%Y', smoke_time)Day, count(1)Nb, strftime('%d', smoke_time)Day, strftime('%m', smoke_time)Month, strftime('%Y', smoke_time)Year  "
                    + "FROM     "
                    + DBConnector.DATABASE_TABLE
                    + " "
                    + "WHERE    strftime('%m', smoke_time) = "
                    + DBFields.PREVIOUS_MONTH
                    + " "
                    + "GROUP BY strftime('%d/%m/%Y', smoke_time)"
                    + "ORDER BY strftime('%d/%m/%Y', smoke_time); ";

            Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

            return DBTools.parseCursorAsSmokeCounterSet(mCursor);
        } catch (Exception e) {
            Log.e(this.getClass().getSimpleName(), "getPreviousDetails", e);
            return null;
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public List<SmokeAverage> getHistoryAverage() {
        Log.i(this.getClass().getSimpleName(), "get Last 6 Months History");
        try {
            String sqlQuery = "SELECT Month, sum(nb), count(nb), round(avg(nb), " + DBTools.ROUNDING + ")" + "FROM  "
                    + "("
                    + "    SELECT   strftime('%m', smoke_time)Month, strftime('%Y-%m-%d', smoke_time)Day, count(1)Nb  "
                    + "    FROM     " + DBConnector.DATABASE_TABLE + " "
                    + "    WHERE    strftime('%Y-%m', smoke_time) > " + DBFields.SIX_MONTHS_AGO + " "
                    + "    GROUP BY strftime('%m', smoke_time), strftime('%d/%m/%Y', smoke_time)ORDER BY smoke_time"
                    + ")" + "GROUP BY Month " + "ORDER BY Day; ";

            Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

            return DBTools.parseCursorAsSmokeAverage(mCursor);
        } catch (Exception e) {
            Log.e(this.getClass().getSimpleName(), "getHistoryAverage", e);
            return null;
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public List<SmokeAverage> getHistoryCost() {
        Log.i(this.getClass().getSimpleName(), "get Last 6 Months Costs");
        try {
            String sqlQuery = "SELECT Month, sum(cost), count(cost), round(sum(ifnull(cost,0)), "
                    + DBTools.ROUNDING
                    + ")"
                    + "FROM "
                    + "("
                    + "    SELECT   strftime('%m', smoke_time)Month, strftime('%Y-%m-%d', smoke_time)Day, sum(smoke_valid)cost  "
                    + "    FROM     " + DBConnector.DATABASE_TABLE + " "
                    + "    WHERE    strftime('%Y-%m', smoke_time) > " + DBFields.SIX_MONTHS_AGO + " "
                    + "    GROUP BY strftime('%m', smoke_time), strftime('%d/%m/%Y', smoke_time)ORDER BY smoke_time"
                    + ")" + "GROUP BY Month " + "ORDER BY Day; ";

            Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

            return DBTools.parseCursorAsSmokeAverage(mCursor);
        } catch (Exception e) {
            Log.e(this.getClass().getSimpleName(), "getHistoryCost", e);
            return null;
        }
    }

}
